首先我們簡單介紹一些時間類型。
no027843
CREATE TABLE dateandtime(a DATE);
INSERT INTO dateandtime(a) VALUES('2020-12-10');
mysql> SELECT * FROM dateandtime;
+------------+
| a |
+------------+
| 2020-12-10 |
+------------+
1 row in set (0.00 sec)
CREATE TABLE dateandtime2 (b TIME);
INSERT INTO dateandtime2(b) VALUES('100525');
mysql> SELECT * FROM dateandtime2;
+----------+
| b |
+----------+
| 10:05:25 |
+----------+
1 rows in set (0.00 sec)
CREATE TABLE year(a YEAR);
INSERT INTO year(a) VALUES('1955');
INSERT INTO year(a) VALUES(2100);
而YEAR比較特殊，我們也可只插入一位或兩位或三位數值來表示。
INSERT INTO year(a) VALUES(0);
INSERT INTO year(a) VALUES('0');
INSERT INTO year(a) VALUES('008');
mysql> SELECT * FROM year;
+------+
| a |
+------+
| 1955 |
| 2100 |
| 0000 |
| 2000 |
| 2008 |
+------+
我們在DATE,TIME,YEAR類型插入時皆以字串類型作為資料。
EX:INSERT INTO dateandtime2(b) VALUES('10:05:25');
但當我們以數值類型插入，也是可以的。
EX:INSERT INTO dateandtime2(b) VALUES(100525);
mysql> SELECT * FROM dateandtime2;
+----------+
| b |
+----------+
| 10:05:25 |
| 10:05:25 |
+----------+
2 rows in set (0.00 sec)
DATETIME 類型 值格式為 'YYYY-MM-DD 'HH:MM:SS'' 範圍為 '1000-01-01 00:00:00' TO '9999-12-31 23:59:59' 也就是DATE類型+TIME類型。
TIMESTAMP 類型 值格式為 'YYYY-MM-DD 'HH:MM:SS'' 範圍為“1970-01-01 00:00:01” UTC ~ “2038-01-19 03:14:07” （和時區有關）
TIMESTAMP&DATETIME區別
1.TIMESTAMP會根據我們時區做改變。DATETIME則不會。
2.TIMESTAMP佔4BYTE空間，DATETIME佔有8BYTE空間。
3.TIMESTAMP不可為空，DATETIME可為空。
如果我們不插入值至TIMESTAMP，預設會顯示當前系統時間。
如同輸入 SELECT NOW(); (可獲取當前時間)
也就是在建立TIMESTAMP時，等同系統自動幫我們設置成這樣。
CREATE TABLE TD(d DATETIME , t TIMESTAMP); 預設等同於下面這段DEFAULT NOW()....
CREATE TABLE TD(d DATETIME , t TIMESTAMP DEFAULT NOW() ON UPDATE NOW());
mysql> DESC TD;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| d | datetime | YES | | NULL | |
| t | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)
當我們改變當前TIMESTAMP所在TABLE裡任何Column的值，TIMESTAMP的值會由於Extra on update的關係而更新一次，實例可應用在如，文章最後更新。
更新TABLE前
mysql> SELECT * FROM TD;
+---------------------+---------------------+
| d | t |
+---------------------+---------------------+
| 2011-05-12 12:15:55 | 2020-12-22 21:14:57 |
+---------------------+---------------------+
1 row in set (0.00 sec)
更新TABLE後(改變column d的值後)，t的時間變為當前時間。
mysql> SELECT * FROM TD;
+---------------------+---------------------+
| d | t |
+---------------------+---------------------+
| 2012-05-12 12:15:55 | 2020-12-22 21:23:18 |
+---------------------+---------------------+
1 row in set (0.00 sec)
首先我們一樣創建TABLE並插入當前時間值
INSERT INTO test_TD2(t,d) VALUES (NOW(),NOW());
我們也可以更改時區，首先先顯示我們SQL的變數，並篩選與time_zone相關的變數。
可以利用SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);找出我們當前UTC時間。
mysql> SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
+--------------------------------+
| TIMEDIFF(NOW(), UTC_TIMESTAMP) |
+--------------------------------+
| 08:00:00 |
+--------------------------------+
1 row in set (0.01 sec)
可發現為以UTC時間為基礎 +8小
如果我們直接利用SET修改時區的值，在SET time_zone="-12:00";
原本時間
mysql> SELECT * FROM test_TD2;
+---------------------+---------------------+
| d | t |
+---------------------+---------------------+
| 2020-12-22 14:27:17 | 2020-12-22 22:27:17 |
+---------------------+---------------------+
1 row in set (0.00 sec)
修改時區後時間
mysql> SELECT * FROM test_TD2;
+---------------------+---------------------+
| d | t |
+---------------------+---------------------+
| 2020-12-22 14:27:17 | 2020-12-22 02:27:17 |
+---------------------+---------------------+
1 row in set (0.00 sec)
會發現時間錯誤。
因為TIMESTAMP是以UTC為基礎。再將時區減去時，才會正確。
所以我們要先將set time_zone='+8:00'; 改為 set time_zone='+0:00'; 也就是變為UTF時間基礎
修改前
mysql> SELECT * FROM test_TD2;
+---------------------+---------------------+
| d | t |
+---------------------+---------------------+
| 2020-12-22 14:27:17 | 2020-12-22 14:27:17 |
+---------------------+---------------------+
1 row in set (0.00 sec)
修改後
mysql> SELECT * FROM test_TD2;
+---------------------+---------------------+
| d | t |
+---------------------+---------------------+
| 2020-12-22 14:27:17 | 2020-12-22 02:27:17 |
+---------------------+---------------------+
1 row in set (0.00 sec)
可發現時區減去12時(14-12=2)已經正確。
CREATE TABLE func(A YEAR,B DATE,C TIME,D DATETIME ,E TIMESTAMP);
INSERT INTO func(a,b,c,d,e) VALUES (2020,20201210,220050,NOW(),NOW());
mysql> SELECT * FROM func;
+------+------------+----------+---------------------+---------------------+
| A | B | C | D | E |
+------+------------+----------+---------------------+---------------------+
| 2020 | 2020-12-10 | 22:00:50 | 2020-12-23 22:01:45 | 2020-12-23 22:01:45 |
+------+------------+----------+---------------------+---------------------+
1 row in set (0.00 sec)
簡單介紹幾個時間的函數。
SELECT CURTIME(); 獲取當前時間
+-----------+
| CURTIME() |
+-----------+
| 22:02:44 |
+-----------+
SELECT CRUDATE(); 獲取當前日期
+------------+
| CURDATE() |
+------------+
| 2020-12-23 |
+------------+
SELECT NOW(); 獲取當前日期+時間
+---------------------+
| NOW() |
+---------------------+
| 2020-12-23 22:03:54 |
+---------------------+
SELECT DAYOFWEEK(B) FROM func; 返回日期為星期幾
(1 = Sunday, 2 = Monday, ., 7 = Saturday...)
+--------------+
| DAYOFWEEK(B) |
+--------------+
| 5 |
+--------------+
SELECT DAYOFMONTH(B) FROM func; 返回月份日期的天數
+---------------+
| DAYOFMONTH(B) |
+---------------+
| 10 |
+---------------+
SELECT DAYOFYEAR(B) FROM func; 返回此日期為當年的第幾天
+--------------+
| DAYOFYEAR(B) |
+--------------+
| 345 |
+--------------+
SELECT MONTHNAME(B) FROM func; 返回日期星期名稱
+--------------+
| MONTHNAME(B) |
+--------------+
| December |
+--------------+
DATE_FORMAT 格式化我們的日期，輸入不同值，日期會以不同方式呈現。
ex : %D 可以轉換日為( 1號 11號..=> st / 2號 12號 ..=> nd / 3號 13號.. => rd /其餘 => th )
%M 顯示月份名稱 %Y顯示年分 等等....
SELECT DATE_FORMAT(hire_date,"%D %M %Y") FROM employee WHERE id="2";
+-----------------------------------+
| DATE_FORMAT(hire_date,"%D %M %Y") |
+-----------------------------------+
| 10th December 2015 |
+-----------------------------------+
iThome鐵人賽
看影片追技術